﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.SalesBook
{
    public class DataLoader
    {
        public static void GetData(Reports.SalesBook.DataSet.DataDataTable data, string ConnectionString, string depName, DateTime dateF, DateTime dateT)
        {
            #region old query
            string QueryString =
@"with Data(DepartName,DocNum,DocDate,CustomerName,CustomerInn,CustomerKpp,Sum,SumNull,Sum0,Sum10,VatSum10,Sum18,VatSum18,PurchaseNumber) as
(
SELECT @DepartName as DepartName, 'Выручка' as DocNum,
dd.DocDate AS DocDate, 
CD.CashRegisters.Name AS CustomerName,
'' as CustomerInn, '' as CustomerKpp,
(SELECT SUM(i.RetailPrice * g.Qntty * -1)
FROM  CA.Documents AS d INNER JOIN
vwGoodsFlow AS g ON d.Id = g.DocId INNER JOIN
vwInputLines AS i ON g.PartyId = i.PartyId
WHERE (g.DocId = dd.Id) AND  g.GodsDoclinesId =vwGoodsFlow.GodsDoclinesId
) AS [Sum],
(SELECT sum(i.RetailPrice * g.Qntty*-1)
FROM  CA.Documents AS d INNER JOIN
vwGoodsFlow AS g ON d.Id = g.DocId INNER JOIN
vwInputLines AS i ON g.PartyId = i.PartyId
WHERE     (g.DocId = dd.Id) AND (i.NDSTax is null)and  g.GodsDoclinesId =vwGoodsFlow.GodsDoclinesId) AS SumNull,
(SELECT sum(i.RetailPrice * g.Qntty*-1)
FROM  CA.Documents AS d INNER JOIN
vwGoodsFlow AS g ON d.Id = g.DocId INNER JOIN
vwInputLines AS i ON g.PartyId = i.PartyId
WHERE     (g.DocId = dd.Id) AND (i.NDSTax = 0)and  g.GodsDoclinesId =vwGoodsFlow.GodsDoclinesId) AS Sum0,
(SELECT sum(i.RetailPrice * g.Qntty * 100 / - 110)
FROM  CA.Documents AS d INNER JOIN
vwGoodsFlow AS g ON d.Id = g.DocId INNER JOIN
vwInputLines AS i ON g.PartyId = i.PartyId
WHERE     (g.DocId = dd.Id) AND (i.NDSTax = 10)and  g.GodsDoclinesId =vwGoodsFlow.GodsDoclinesId) AS Sum10, 
(SELECT sum(i.RetailPrice * g.Qntty * 10 / - 110)
FROM  CA.Documents AS d INNER JOIN
vwGoodsFlow AS g ON d.Id = g.DocId INNER JOIN
vwInputLines AS i ON g.PartyId = i.PartyId
WHERE     (g.DocId = dd.Id) AND (i.NDSTax = 10)and  g.GodsDoclinesId =vwGoodsFlow.GodsDoclinesId) AS VatSum10, 
(SELECT sum(i.RetailPrice * g.Qntty * 100 / - 118)
FROM  CA.Documents AS d INNER JOIN
vwGoodsFlow AS g ON d.Id = g.DocId INNER JOIN
vwInputLines AS i ON g.PartyId = i.PartyId
WHERE     (g.DocId = dd.Id) AND (i.NDSTax = 18)and  g.GodsDoclinesId =vwGoodsFlow.GodsDoclinesId) AS Sum18, 
(SELECT sum(i.RetailPrice * g.Qntty * 18 / - 118)
FROM  CA.Documents AS d INNER JOIN
vwGoodsFlow AS g ON d.Id = g.DocId INNER JOIN
vwInputLines AS i ON g.PartyId = i.PartyId
WHERE     (g.DocId = dd.Id) AND (i.NDSTax = 18) and g.GodsDoclinesId =vwGoodsFlow.GodsDoclinesId ) AS VatSum18,
null as PurchaseNumber 

FROM         CA.Documents dd INNER JOIN
vwGoodsFlow ON dd.Id = vwGoodsFlow.DocId INNER JOIN
CD.CashRegisterShifts ON dd.CashRegisterShiftId = CD.CashRegisterShifts.Id AND 
dd.CashRegisterShiftId = CD.CashRegisterShifts.Id INNER JOIN
CD.CashRegisters ON CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id AND 
CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id INNER JOIN
CD.Parties ON vwGoodsFlow.PartyId = CD.Parties.Id 
WHERE     (dd.DocTypeId = 3) and dd.DocDate BETWEEN @DateF AND @DateT
)
select DepartName,DocNum,convert(datetime, convert(varchar, DocDate, 104), 104)as DocDate,CustomerName,CustomerInn,CustomerKpp,Sum([Sum]) as [Sum],
sum(SumNull) as [Sumnull],sum(Sum0) as Sum0,sum(Sum10) as Sum10,sum(VatSum10) as VatSum10,sum(Sum18)as Sum18,sum(VatSum18)as VatSum18,PurchaseNumber 
from Data
group by convert(datetime, convert(varchar, DocDate, 104), 104),CustomerName,CustomerInn,CustomerKpp,DepartName,DocNum,PurchaseNumber";
            #endregion
            string QueryString1 = @"with 
Data(DepartName,DocNum,DocDate,CustomerName,CustomerInn,CustomerKpp,Sum,SumNull,Sum0,Sum10,VatSum10,Sum18,VatSum18,PurchaseNumber) as
(
SELECT   isnull(CO.Departments.Name, '' ) as DepartName,'Выручка' as DocNum,  
convert(datetime, convert(varchar, CA.Documents.DocDate, 104), 104)as DocDate, 
CD.CashRegisters.Name as CustomerName,'' as CustomerInn, '' as CustomerKpp,
(CA.GoodsDocLines.RetailSum * - 1) as [Sum],-- CD.InputLines.NDSTax,
CASE WHEN (CD.InputLines.NDSTax IS NULL) THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END as SumNull,
CASE WHEN (CD.InputLines.NDSTax = 0) THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END as Sum0,
CASE WHEN CD.InputLines.NDSTax = 10 THEN CA.GoodsDocLines.RetailSum * 100 / -110 ELSE 0 END AS Sum10,
CASE WHEN CD.InputLines.NDSTax = 10 THEN CA.GoodsDocLines.RetailSum * 10 / -110 ELSE 0 END AS VatSum10,
CASE WHEN CD.InputLines.NDSTax = 18 THEN CA.GoodsDocLines.RetailSum * 100 / -118 ELSE 0 END AS Sum18,
CASE WHEN CD.InputLines.NDSTax = 18 THEN CA.GoodsDocLines.RetailSum * 18 / -118 ELSE 0 END AS VatSum18,
null as PurchaseNumber                     
FROM         CA.Documents INNER JOIN
CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id INNER JOIN
CD.InputLines ON CD.Parties.InputLineId = CD.InputLines.Id INNER JOIN
CD.CashRegisterShifts ON CA.Documents.CashRegisterShiftId = CD.CashRegisterShifts.Id AND 
CA.Documents.CashRegisterShiftId = CD.CashRegisterShifts.Id INNER JOIN
CD.CashRegisters ON CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id LEFT OUTER JOIN
CO.Departments ON CA.Documents.DepartmentId = CO.Departments.Id
WHERE     (CA.Documents.DocTypeId = 3) AND CA.Documents.DocDate BETWEEN @DateF AND @DateT
)
select DepartName,DocNum,DocDate,CustomerName,CustomerInn,CustomerKpp,Sum([Sum]) as [Sum],
sum(SumNull) as [Sumnull],sum(Sum0) as Sum0,sum(Sum10) as Sum10,sum(VatSum10) as VatSum10,
sum(Sum18)as Sum18, sum(VatSum18)as VatSum18,PurchaseNumber 
from Data
group by DocDate, CustomerName,CustomerInn,CustomerKpp,DepartName,DocNum,PurchaseNumber";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString1, connection);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                   // SqlParameter DepartName = new SqlParameter("@DepartName", SqlDbType.VarChar, 25);
                  //  DepartName.Value = depName;
                  //  command.Parameters.Add(DepartName);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }

        }
        public static void GetInfo(Reports.SalesBook.DataSet.InfoDataTable info, string ConnectionString, DateTime dateF, DateTime dateT)
        {
            string QueryString =
@"SELECT     Name , INN, KPP
FROM         CA.Contragents
WHERE     (Id = 0)";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        Int32 inn, name, kpp;
                        Reports.SalesBook.DataSet.InfoRow ir = info.NewInfoRow();
                        ir.AptekaInn = string.Empty;
                        ir.AptekaKpp = string.Empty;
                        ir.AptekaName = string.Empty;
                        ir.DateF = dateF;
                        ir.DateT = dateT;
                        inn = reader.GetOrdinal("INN");
                        kpp = reader.GetOrdinal("KPP");
                        name = reader.GetOrdinal("Name");
                        if (!reader.IsDBNull(name))
                        {
                            ir.AptekaName = (string)reader[name];
                        }
                        if (!reader.IsDBNull(kpp))
                        {
                            ir.AptekaKpp = (string)reader[kpp];
                        }
                        if (!reader.IsDBNull(inn))
                        {
                            ir.AptekaInn = (string)reader[inn];
                        }
                        info.Rows.Add(ir);

                    }
                    else
                    {
                        throw new Exception("Невозможно прочитать данные");
                    }
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
